IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_EntitySearchCmsPage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[cms_sp_EntitySearchCmsPage]
GO

create procedure [dbo].[cms_sp_EntitySearchCmsPage]
(@SearchQuery varchar(max),
 @IsInAdministrationArea bit = null,
 @SiteGroupId int = null,
 @SiteId int = null
)
AS

declare @Searched table 
(
	PageId int not null	
)

insert into @Searched (PageId)
select PageId
  from 
	(SELECT P.PageId,
			COALESCE(P.PageKeywords, '') + ' ' +
			COALESCE(P.PageName, '') + ' ' +
			COALESCE(P.PageDescription, '') + ' ' +
			COALESCE(P.PageTitle, '') + ' ' +
			COALESCE(P.PageTitleOverride,'') + ' ' +
		    COALESCE(
		   [dbo].cms_udf_StripHTML(
		   (
			SELECT C.ContentBody + ' '
			  FROM [dbo].[PageControls] PC,
				   [dbo].[Contents] C
			 WHERE PC.PageControlContentId=C.ContentId 
			   AND PC.PageId=P.PageId
			 FOR XML PATH('')  
		   )),'')
			as Content
	  FROM [dbo].[Pages] As P 
			join [dbo].[EntityRevisions] as R
				on 	R.EntityId=P.PageId AND R.EntityTypeParameter='Page'
				and ((@IsInAdministrationArea = 1 and R.IsLatestRevision = 1) or
					(@IsInAdministrationArea = 0 and R.IsLatestCompletedRevision = 1))
	 WHERE P.IsVisible=1 
	   and((P.SiteGroupId is null and P.SiteId is null) or 
		(P.SiteGroupId = @SiteGroupId and P.SiteId is null) or
		(P.SiteGroupId = @SiteGroupId and P.SiteId = @SiteId))
	) PageContents
where Content like '%'+@searchQuery+'%'

select Pages.*
  from @Searched S inner join [dbo].[Pages] on Pages.PageId = S.PageId
  
go